/*=====================================================================
  This file is part of a Microsoft SQL Server Shared Source Application.
  Copyright (C) Microsoft Corporation.  All rights reserved.
 
THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
PARTICULAR PURPOSE.
======================================================= */
USE [master]
GO
if exists (
	select * from sys.databases where name = 'AdventureWorks2008_dbss')
	drop database AdventureWorks2008_dbss
GO
USE [AdventureWorks2008]
IF EXISTS (
	SELECT is_cdc_enabled from sys.databases
	WHERE name = 'AdventureWorks2008'
	AND is_cdc_enabled = 1)
	exec sp_cdc_disable_db
GO
IF EXISTS (
	SELECT is_cdc_enabled from sys.databases
	WHERE name = 'AdventureWorks2008'
	AND is_cdc_enabled = 0)
	exec sp_cdc_enable_db
GO
if object_id(N'CDCSample.Customer', 'U') is not null
		drop table CDCSample.Customer
go
if object_id(N'CDCSample.CreditCard', 'U') is not null
		drop table CDCSample.CreditCard
go
if object_id(N'CDCSample.WorkOrder', 'U') is not null
		drop table CDCSample.WorkOrder
go
if object_id(N'dbo.fn_net_changes_Customer', 'TF') is not null
		drop function dbo.fn_net_changes_Customer
go
if object_id(N'dbo.fn_net_changes_CreditCard', 'TF') is not null
		drop function dbo.fn_net_changes_CreditCard
go
if object_id(N'dbo.fn_net_changes_WorkOrder', 'TF') is not null
		drop function dbo.fn_net_changes_WorkOrder
go
if object_id(N'dbo.fn_net_changes_Customer_First', 'TF') is not null
		drop function dbo.fn_net_changes_Customer_First
go
if object_id(N'dbo.fn_net_changes_CreditCard_First', 'TF') is not null
		drop function dbo.fn_net_changes_CreditCard_First
go
if object_id(N'dbo.fn_net_changes_WorkOrder_First', 'TF') is not null
		drop function dbo.fn_net_changes_WorkOrder_First
go
if object_id(N'CDCSample.generate_wrappers', 'P') is not null
		drop procedure CDCSample.generate_wrappers
go
if exists (
	select name	from sys.schemas
	where name = N'CDCSample')
	drop schema CDCSample
go
CREATE SCHEMA CDCSample
GO
CREATE TABLE [CDCSample].[Customer](
	[CustomerID] [int] NOT NULL,
	[PersonID] [int] NULL,
	[StoreID] [int] NULL,
	[TerritoryID] [int] NULL,
	[AccountNumber]  AS (isnull('AW'+[dbo].[ufnLeadingZeros]([CustomerID]),'')),
	[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF_Customer_rowguid]  DEFAULT (newid()),
	[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Customer_ModifiedDate]  DEFAULT (getdate()),
 CONSTRAINT [CDC_PK_Customer_CustomerID] PRIMARY KEY CLUSTERED 
(
	[CustomerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [CDCSample].[CreditCard](
	[CreditCardID] [int] NOT NULL,
	[CardType] [nvarchar](50) NOT NULL,
	[CardNumber] [nvarchar](25) NOT NULL,
	[ExpMonth] [tinyint] NOT NULL,
	[ExpYear] [smallint] NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_CreditCard_CreditCardID] PRIMARY KEY CLUSTERED 
(
	[CreditCardID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [CDCSample].[WorkOrder](
	[WorkOrderID] [int] NOT NULL,
	[ProductID] [int] NOT NULL,
	[OrderQty] [int] NOT NULL,
	[StockedQty]  AS (isnull([OrderQty]-[ScrappedQty],(0))),
	[ScrappedQty] [smallint] NOT NULL,
	[StartDate] [datetime] NOT NULL,
	[EndDate] [datetime] NULL,
	[DueDate] [datetime] NOT NULL,
	[ScrapReasonID] [smallint] NULL,
	[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_WorkOrder_ModifiedDate]  DEFAULT (getdate()),
 CONSTRAINT [CDC_PK_WorkOrder_WorkOrderID] PRIMARY KEY CLUSTERED 
(
	[WorkOrderID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE procedure [CDCSample].[generate_wrappers]
as
begin
declare @wrapper_functions table(function_name sysname, create_stmt nvarchar(max))
insert into @wrapper_functions
exec sys.sp_cdc_generate_wrapper_function
declare @stmt nvarchar(max)
declare #hfunctions cursor local fast_forward for
	select create_stmt from @wrapper_functions
	where function_name like 'fn_net_changes%'
open #hfunctions
fetch #hfunctions into @stmt
while (@@fetch_status <> -1)
begin
	exec sp_executesql @stmt
	fetch #hfunctions into @stmt
end
close #hfunctions
deallocate #hfunctions
end
GO
USE [AdventureWorksDW2008]
GO
if object_id(N'CDCSample.Customer', 'U') is not null
		drop table CDCSample.Customer
go
if object_id(N'CDCSample.CreditCard', 'U') is not null
		drop table CDCSample.CreditCard
go
if object_id(N'CDCSample.WorkOrder', 'U') is not null
		drop table CDCSample.WorkOrder
go
if exists (
	select name	from sys.schemas
	where name = N'CDCSample')
	drop schema CDCSample
go
if object_id(N'dbo.ufnLeadingZeros', 'FN') is not null
		drop function dbo.ufnLeadingZeros
go
CREATE FUNCTION [dbo].[ufnLeadingZeros](
    @Value int
) 
RETURNS varchar(8) 
WITH SCHEMABINDING 
AS 
BEGIN
    DECLARE @ReturnValue varchar(8);

    SET @ReturnValue = CONVERT(varchar(8), @Value);
    SET @ReturnValue = REPLICATE('0', 8 - DATALENGTH(@ReturnValue)) + @ReturnValue;

    RETURN (@ReturnValue);
END;
GO
CREATE SCHEMA CDCSample
GO
CREATE TABLE [CDCSample].[Customer](
	[CustomerID] [int] NOT NULL,
	[PersonID] [int] NULL,
	[StoreID] [int] NULL,
	[TerritoryID] [int] NULL,
	[AccountNumber]  AS (isnull('AW'+[dbo].[ufnLeadingZeros]([CustomerID]),'')),
	[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [CDC_PK_Customer_CustomerID] PRIMARY KEY CLUSTERED 
(
	[CustomerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [CDCSample].[CreditCard](
	[CreditCardID] [int] NOT NULL,
	[CardType] [nvarchar](50) NOT NULL,
	[CardNumber] [nvarchar](25) NOT NULL,
	[ExpMonth] [tinyint] NOT NULL,
	[ExpYear] [smallint] NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_CreditCard_CreditCardID] PRIMARY KEY CLUSTERED 
(
	[CreditCardID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [CDCSample].[WorkOrder](
	[WorkOrderID] [int] NOT NULL,
	[ProductID] [int] NOT NULL,
	[OrderQty] [int] NOT NULL,
	[StockedQty]  AS (isnull([OrderQty]-[ScrappedQty],(0))),
	[ScrappedQty] [smallint] NOT NULL,
	[StartDate] [datetime] NOT NULL,
	[EndDate] [datetime] NULL,
	[DueDate] [datetime] NOT NULL,
	[ScrapReasonID] [smallint] NULL,
	[ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [CDC_PK_WorkOrder_WorkOrderID] PRIMARY KEY CLUSTERED 
(
	[WorkOrderID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
USE AdventureWorks2008
go
insert into CDCSample.Customer
select CustomerID, PersonID, StoreID, TerritoryID, rowguid, getdate()
from Sales.Customer
where CustomerID < 8000
go
-- Enable change data capture for the table
exec sys.sp_cdc_enable_table 'CDCSample', 'Customer', 'Customer', @supports_net_changes = 1, @role_name = null
go
insert into CDCSample.CreditCard
select CreditCardID, CardType, CardNumber, ExpMonth, ExpYear, getdate()
from Sales.CreditCard
where CreditCardID < 8000
go
-- Enable change data capture for the table
exec sys.sp_cdc_enable_table 'CDCSample', 'CreditCard', 'CreditCard', @supports_net_changes = 1, @role_name = null
go
insert into CDCSample.WorkOrder
select WorkOrderID, ProductID, OrderQty, ScrappedQty, StartDate, EndDate, DueDate, ScrapReasonID, getdate()
from Production.WorkOrder
where  ModifiedDate < CONVERT(datetime,'20020718',112)
go
-- Enable change data capture for the table
exec sys.sp_cdc_enable_table 'CDCSample', 'WorkOrder', 'WorkOrder', @supports_net_changes = 1, @role_name = null
go
-- Generate wrapper functions
exec CDCSample.generate_wrappers
gO
-- Generate custom wrapper functions for initial incremental load of Customer
create function [dbo].[fn_net_changes_Customer_First]
(	@start_lsn_str varchar(40) = null,
	@end_time datetime = null,
	@row_filter_option nvarchar(30) = N'all'
)
returns @resultset table (  [CustomerID] int, [PersonID] int, [StoreID] int, [TerritoryID] int, [AccountNumber] varchar(10), [rowguid] uniqueidentifier, [ModifiedDate] datetime,  [__CDC_OPERATION] varchar(2)  
) as
begin
	declare @from_lsn binary(10), @to_lsn binary(10), @start_lsn binary(10) 
	
	if (@start_lsn_str is null)
		select @from_lsn = [sys].[fn_cdc_get_min_lsn]('Customer')
	else
	begin
           select @start_lsn = [sys].[fn_cdc_hexstrtobin](@start_lsn_str)  
		if ([sys].[fn_cdc_get_min_lsn]('Customer') > @start_lsn) or
		   ([sys].[fn_cdc_get_max_lsn]() < @start_lsn)
			select @from_lsn = null
		else
			select @from_lsn = [sys].[fn_cdc_increment_lsn](@start_lsn)
	end	

	if (@end_time is null)
		select @to_lsn = [sys].[fn_cdc_get_max_lsn]()
	else
	begin
		if [sys].[fn_cdc_map_lsn_to_time]([sys].[fn_cdc_get_max_lsn]()) < @end_time
			select @to_lsn = null
		else
			select @to_lsn = [sys].[fn_cdc_map_time_to_lsn]('largest less than or equal',@end_time)
	end
	
	if @from_lsn is not null and @to_lsn is not null and
		(@from_lsn = [sys].[fn_cdc_increment_lsn](@to_lsn))
		return
		
	insert into @resultset
	select  [CustomerID], [PersonID], [StoreID], [TerritoryID], [AccountNumber], [rowguid], [ModifiedDate], 
		case [__$operation]
			when 1 then 'D'
			when 2 then 'I'
			when 3 then 'UO'
			when 4 then 'UN'
			when 5 then 'M'
			else null
		end as [__CDC_OPERATION] 		
	from [cdc].[fn_cdc_get_net_changes_Customer](@from_lsn, @to_lsn, @row_filter_option)  
	
	return
end
GO
-- Generate custom wrapper functions for initial incremental load of CreditCard
create function [dbo].[fn_net_changes_CreditCard_First]
(	@start_lsn_str varchar(40) = null,
	@end_time datetime = null,
	@row_filter_option nvarchar(30) = N'all'
)
returns @resultset table (  [CreditCardID] int, [CardType] nvarchar(50), [CardNumber] nvarchar(25), ExpMonth tinyint, [ExpYear] smallint, [ModifiedDate] datetime, [__CDC_OPERATION] varchar(2) 
) as
begin
	declare @from_lsn binary(10), @to_lsn binary(10), @start_lsn binary(10) 
		
	if (@start_lsn_str is null)
		select @from_lsn = [sys].[fn_cdc_get_min_lsn]('CreditCard')
	else
	begin
           select @start_lsn = [sys].[fn_cdc_hexstrtobin](@start_lsn_str)  
		if ([sys].[fn_cdc_get_min_lsn]('CreditCard') > @start_lsn) or
		   ([sys].[fn_cdc_get_max_lsn]() < @start_lsn)
			select @from_lsn = null
		else
			select @from_lsn = [sys].[fn_cdc_increment_lsn](@start_lsn)
	end	
	
	if (@end_time is null)
		select @to_lsn = [sys].[fn_cdc_get_max_lsn]()
	else
	begin
		if [sys].[fn_cdc_map_lsn_to_time]([sys].[fn_cdc_get_max_lsn]()) < @end_time
			select @to_lsn = null
		else
			select @to_lsn = [sys].[fn_cdc_map_time_to_lsn]('largest less than or equal',@end_time)
	end
		
	if @from_lsn is not null and @to_lsn is not null and
		(@from_lsn = [sys].[fn_cdc_increment_lsn](@to_lsn))
		return
		
	insert into @resultset
	select  [CreditCardID],	[CardType], [CardNumber], [ExpMonth], [ExpYear], [ModifiedDate], 
		case [__$operation]
			when 1 then 'D'
			when 2 then 'I'
			when 3 then 'UO'
			when 4 then 'UN'
			when 5 then 'M'
			else null
		end as [__CDC_OPERATION] 		
	from [cdc].[fn_cdc_get_net_changes_CreditCard](@from_lsn, @to_lsn, @row_filter_option)  
		
	return
end
GO
-- Generate custom wrapper functions for initial incremental load of WorkOrder
create function [dbo].[fn_net_changes_WorkOrder_First]
(	@start_lsn_str varchar(40) = null,
	@end_time datetime = null,
	@row_filter_option nvarchar(30) = N'all'
)
returns @resultset table (  [WorkOrderID] int, [ProductID] int, [OrderQty] int, [StockedQty] int, [ScrappedQty] smallint, [StartDate] datetime, [EndDate] datetime, [DueDate] datetime, [ScrapReasonID] smallint, [ModifiedDate] datetime,  [__CDC_OPERATION] varchar(2)  
) as
begin
	declare @from_lsn binary(10), @to_lsn binary(10), @start_lsn binary(10) 
		
	if (@start_lsn_str is null)
		select @from_lsn = [sys].[fn_cdc_get_min_lsn]('WorkOrder')
	else
	begin
           select @start_lsn = [sys].[fn_cdc_hexstrtobin](@start_lsn_str)  
		if ([sys].[fn_cdc_get_min_lsn]('WorkOrder') > @start_lsn) or
			   ([sys].[fn_cdc_get_max_lsn]() < @start_lsn)
			select @from_lsn = null
		else
			select @from_lsn = [sys].[fn_cdc_increment_lsn](@start_lsn)
	end	
	
	if (@end_time is null)
		select @to_lsn = [sys].[fn_cdc_get_max_lsn]()
	else
	begin
		if [sys].[fn_cdc_map_lsn_to_time]([sys].[fn_cdc_get_max_lsn]()) < @end_time
			select @to_lsn = null
		else
			select @to_lsn = [sys].[fn_cdc_map_time_to_lsn]('largest less than or equal',@end_time)
	end
		
	if @from_lsn is not null and @to_lsn is not null and
		(@from_lsn = [sys].[fn_cdc_increment_lsn](@to_lsn))
		return
			
	insert into @resultset
	select  [WorkOrderID], [ProductID], [OrderQty], [StockedQty], [ScrappedQty], [StartDate], [EndDate], [DueDate], [ScrapReasonID], [ModifiedDate], 
		case [__$operation]
			when 1 then 'D'
			when 2 then 'I'
			when 3 then 'UO'
			when 4 then 'UN'
			when 5 then 'M'
			else null
		end as [__CDC_OPERATION] 		
	from [cdc].[fn_cdc_get_net_changes_WorkOrder](@from_lsn, @to_lsn, @row_filter_option)  

	return
end
GO






